
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/22/2013 18:31:05
-- Generated from EDMX file: C:\Users\carlos.montoya.plaza\Desktop\PFC\COEVERY\20130815_coeverylex\coeverylex\Coevery.Domain\EntityModel\AdventureWorks.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [AdventureWorks];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CarteraInmueble]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inmueble] DROP CONSTRAINT [FK_CarteraInmueble];
GO
IF OBJECT_ID(N'[dbo].[FK_CarteraMadre]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cartera] DROP CONSTRAINT [FK_CarteraMadre];
GO
IF OBJECT_ID(N'[dbo].[FK_ComunidadAutonomaProvincia]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Provincia] DROP CONSTRAINT [FK_ComunidadAutonomaProvincia];
GO
IF OBJECT_ID(N'[dbo].[FK_DetalleInmuebleInmueble]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DetalleInmueble] DROP CONSTRAINT [FK_DetalleInmuebleInmueble];
GO
IF OBJECT_ID(N'[dbo].[FK_DetalleInmuebleTipoEdificio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DetalleInmueble] DROP CONSTRAINT [FK_DetalleInmuebleTipoEdificio];
GO
IF OBJECT_ID(N'[dbo].[FK_DetalleVivienda_inherits_DetalleInmueble]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DetalleInmueble_DetalleVivienda] DROP CONSTRAINT [FK_DetalleVivienda_inherits_DetalleInmueble];
GO
IF OBJECT_ID(N'[dbo].[FK_DetalleViviendaEstadoCalidad]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DetalleInmueble_DetalleVivienda] DROP CONSTRAINT [FK_DetalleViviendaEstadoCalidad];
GO
IF OBJECT_ID(N'[dbo].[FK_InmuebleEdificio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Edificio] DROP CONSTRAINT [FK_InmuebleEdificio];
GO
IF OBJECT_ID(N'[dbo].[FK_InmuebleMunicipio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inmueble] DROP CONSTRAINT [FK_InmuebleMunicipio];
GO
IF OBJECT_ID(N'[dbo].[FK_InmuebleTasacion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tasacion] DROP CONSTRAINT [FK_InmuebleTasacion];
GO
IF OBJECT_ID(N'[dbo].[FK_ProvinciaMunicipio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Municipio] DROP CONSTRAINT [FK_ProvinciaMunicipio];
GO
IF OBJECT_ID(N'[dbo].[FK_TasacionBasaEnInmueble]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inmueble] DROP CONSTRAINT [FK_TasacionBasaEnInmueble];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Cartera]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cartera];
GO
IF OBJECT_ID(N'[dbo].[ComunidadAutonoma]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ComunidadAutonoma];
GO
IF OBJECT_ID(N'[dbo].[DetalleInmueble]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DetalleInmueble];
GO
IF OBJECT_ID(N'[dbo].[DetalleInmueble_DetalleVivienda]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DetalleInmueble_DetalleVivienda];
GO
IF OBJECT_ID(N'[dbo].[Edificio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Edificio];
GO
IF OBJECT_ID(N'[dbo].[EstadoCalidad]', 'U') IS NOT NULL
    DROP TABLE [dbo].[EstadoCalidad];
GO
IF OBJECT_ID(N'[dbo].[Inmueble]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Inmueble];
GO
IF OBJECT_ID(N'[dbo].[Municipio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Municipio];
GO
IF OBJECT_ID(N'[dbo].[Provincia]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Provincia];
GO
IF OBJECT_ID(N'[dbo].[Tasacion]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tasacion];
GO
IF OBJECT_ID(N'[dbo].[TipoEdificio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TipoEdificio];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Cartera'
CREATE TABLE [dbo].[Cartera] (
    [CarteraId] uniqueidentifier  NOT NULL,
    [NombreCartera] nvarchar(max)  NOT NULL,
    [CarteraCarteraId] uniqueidentifier  NULL
);
GO

-- Creating table 'ComunidadAutonoma'
CREATE TABLE [dbo].[ComunidadAutonoma] (
    [ComAutId] uniqueidentifier  NOT NULL,
    [Nombre] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'DetalleInmueble'
CREATE TABLE [dbo].[DetalleInmueble] (
    [DetalleId] uniqueidentifier  NOT NULL,
    [TipoEdificioTipoId] uniqueidentifier  NOT NULL,
    [Inmueble_PropertyId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'DetalleInmueble_DetalleVivienda'
CREATE TABLE [dbo].[DetalleInmueble_DetalleVivienda] (
    [Habitaciones] smallint  NULL,
    [Dormitorios] smallint  NULL,
    [Baños] smallint  NULL,
    [Amueblado] bit  NULL,
    [CocinaEquipada] bit  NULL,
    [Garaje] bit  NULL,
    [Trastero] bit  NULL,
    [Terraza] smallint  NULL,
    [Jardin] bit  NULL,
    [Exterior] bit  NULL,
    [Aire] bit  NULL,
    [Calefaccion] bit  NULL,
    [Ascensor] bit  NULL,
    [Estado] nvarchar(max)  NULL,
    [EstadoCalidadEstadoId] uniqueidentifier  NULL,
    [DetalleId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Edificio'
CREATE TABLE [dbo].[Edificio] (
    [EdificioId] uniqueidentifier  NOT NULL,
    [AñoConstruccion] smallint  NULL,
    [AñoReforma] smallint  NULL,
    [MetrosConstruidos] int  NULL,
    [MetrosUtiles] int  NULL,
    [ReferenciaCatastral] nvarchar(max)  NULL
);
GO

-- Creating table 'EstadoCalidad'
CREATE TABLE [dbo].[EstadoCalidad] (
    [EstadoId] uniqueidentifier  NOT NULL,
    [Valor] smallint  NOT NULL,
    [Etiqueta] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Inmueble'
CREATE TABLE [dbo].[Inmueble] (
    [PropertyId] uniqueidentifier  NOT NULL,
    [Barrio] nvarchar(max)  NULL,
    [TipoVia] uniqueidentifier  NOT NULL,
    [NombreVia] nvarchar(max)  NOT NULL,
    [Numero] smallint  NOT NULL,
    [Escalera] nvarchar(max)  NULL,
    [Planta] smallint  NULL,
    [Latitud] decimal(18,0)  NULL,
    [Longitud] decimal(18,0)  NULL,
    [TasacionTasacionId] uniqueidentifier  NULL,
    [Propio] bit  NULL,
    [CarteraCarteraId] uniqueidentifier  NOT NULL,
    [MunicipioMunicipioId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Municipio'
CREATE TABLE [dbo].[Municipio] (
    [MunicipioId] uniqueidentifier  NOT NULL,
    [Nombre] nvarchar(max)  NOT NULL,
    [ProvinciaProvinciaId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Provincia'
CREATE TABLE [dbo].[Provincia] (
    [ProvinciaId] uniqueidentifier  NOT NULL,
    [Nombre] nvarchar(max)  NOT NULL,
    [ComunidadAutonomaComAutId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Tasacion'
CREATE TABLE [dbo].[Tasacion] (
    [TasacionId] uniqueidentifier  NOT NULL,
    [Valor] float  NOT NULL,
    [ValorM2] float  NOT NULL,
    [ValorVentaRapida] float  NOT NULL,
    [ValorMinimo] float  NOT NULL,
    [InmueblePropertyId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'TipoEdificio'
CREATE TABLE [dbo].[TipoEdificio] (
    [TipoId] uniqueidentifier  NOT NULL,
    [CodigoTipo] nvarchar(max)  NOT NULL,
    [NombreTipo] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CarteraId] in table 'Cartera'
ALTER TABLE [dbo].[Cartera]
ADD CONSTRAINT [PK_Cartera]
    PRIMARY KEY CLUSTERED ([CarteraId] ASC);
GO

-- Creating primary key on [ComAutId] in table 'ComunidadAutonoma'
ALTER TABLE [dbo].[ComunidadAutonoma]
ADD CONSTRAINT [PK_ComunidadAutonoma]
    PRIMARY KEY CLUSTERED ([ComAutId] ASC);
GO

-- Creating primary key on [DetalleId] in table 'DetalleInmueble'
ALTER TABLE [dbo].[DetalleInmueble]
ADD CONSTRAINT [PK_DetalleInmueble]
    PRIMARY KEY CLUSTERED ([DetalleId] ASC);
GO

-- Creating primary key on [DetalleId] in table 'DetalleInmueble_DetalleVivienda'
ALTER TABLE [dbo].[DetalleInmueble_DetalleVivienda]
ADD CONSTRAINT [PK_DetalleInmueble_DetalleVivienda]
    PRIMARY KEY CLUSTERED ([DetalleId] ASC);
GO

-- Creating primary key on [EdificioId] in table 'Edificio'
ALTER TABLE [dbo].[Edificio]
ADD CONSTRAINT [PK_Edificio]
    PRIMARY KEY CLUSTERED ([EdificioId] ASC);
GO

-- Creating primary key on [EstadoId] in table 'EstadoCalidad'
ALTER TABLE [dbo].[EstadoCalidad]
ADD CONSTRAINT [PK_EstadoCalidad]
    PRIMARY KEY CLUSTERED ([EstadoId] ASC);
GO

-- Creating primary key on [PropertyId] in table 'Inmueble'
ALTER TABLE [dbo].[Inmueble]
ADD CONSTRAINT [PK_Inmueble]
    PRIMARY KEY CLUSTERED ([PropertyId] ASC);
GO

-- Creating primary key on [MunicipioId] in table 'Municipio'
ALTER TABLE [dbo].[Municipio]
ADD CONSTRAINT [PK_Municipio]
    PRIMARY KEY CLUSTERED ([MunicipioId] ASC);
GO

-- Creating primary key on [ProvinciaId] in table 'Provincia'
ALTER TABLE [dbo].[Provincia]
ADD CONSTRAINT [PK_Provincia]
    PRIMARY KEY CLUSTERED ([ProvinciaId] ASC);
GO

-- Creating primary key on [TasacionId] in table 'Tasacion'
ALTER TABLE [dbo].[Tasacion]
ADD CONSTRAINT [PK_Tasacion]
    PRIMARY KEY CLUSTERED ([TasacionId] ASC);
GO

-- Creating primary key on [TipoId] in table 'TipoEdificio'
ALTER TABLE [dbo].[TipoEdificio]
ADD CONSTRAINT [PK_TipoEdificio]
    PRIMARY KEY CLUSTERED ([TipoId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CarteraCarteraId] in table 'Inmueble'
ALTER TABLE [dbo].[Inmueble]
ADD CONSTRAINT [FK_CarteraInmueble]
    FOREIGN KEY ([CarteraCarteraId])
    REFERENCES [dbo].[Cartera]
        ([CarteraId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CarteraInmueble'
CREATE INDEX [IX_FK_CarteraInmueble]
ON [dbo].[Inmueble]
    ([CarteraCarteraId]);
GO

-- Creating foreign key on [CarteraCarteraId] in table 'Cartera'
ALTER TABLE [dbo].[Cartera]
ADD CONSTRAINT [FK_CarteraMadre]
    FOREIGN KEY ([CarteraCarteraId])
    REFERENCES [dbo].[Cartera]
        ([CarteraId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CarteraMadre'
CREATE INDEX [IX_FK_CarteraMadre]
ON [dbo].[Cartera]
    ([CarteraCarteraId]);
GO

-- Creating foreign key on [ComunidadAutonomaComAutId] in table 'Provincia'
ALTER TABLE [dbo].[Provincia]
ADD CONSTRAINT [FK_ComunidadAutonomaProvincia]
    FOREIGN KEY ([ComunidadAutonomaComAutId])
    REFERENCES [dbo].[ComunidadAutonoma]
        ([ComAutId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ComunidadAutonomaProvincia'
CREATE INDEX [IX_FK_ComunidadAutonomaProvincia]
ON [dbo].[Provincia]
    ([ComunidadAutonomaComAutId]);
GO

-- Creating foreign key on [Inmueble_PropertyId] in table 'DetalleInmueble'
ALTER TABLE [dbo].[DetalleInmueble]
ADD CONSTRAINT [FK_DetalleInmuebleInmueble]
    FOREIGN KEY ([Inmueble_PropertyId])
    REFERENCES [dbo].[Inmueble]
        ([PropertyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DetalleInmuebleInmueble'
CREATE INDEX [IX_FK_DetalleInmuebleInmueble]
ON [dbo].[DetalleInmueble]
    ([Inmueble_PropertyId]);
GO

-- Creating foreign key on [TipoEdificioTipoId] in table 'DetalleInmueble'
ALTER TABLE [dbo].[DetalleInmueble]
ADD CONSTRAINT [FK_DetalleInmuebleTipoEdificio]
    FOREIGN KEY ([TipoEdificioTipoId])
    REFERENCES [dbo].[TipoEdificio]
        ([TipoId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DetalleInmuebleTipoEdificio'
CREATE INDEX [IX_FK_DetalleInmuebleTipoEdificio]
ON [dbo].[DetalleInmueble]
    ([TipoEdificioTipoId]);
GO

-- Creating foreign key on [DetalleId] in table 'DetalleInmueble_DetalleVivienda'
ALTER TABLE [dbo].[DetalleInmueble_DetalleVivienda]
ADD CONSTRAINT [FK_DetalleVivienda_inherits_DetalleInmueble]
    FOREIGN KEY ([DetalleId])
    REFERENCES [dbo].[DetalleInmueble]
        ([DetalleId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [EstadoCalidadEstadoId] in table 'DetalleInmueble_DetalleVivienda'
ALTER TABLE [dbo].[DetalleInmueble_DetalleVivienda]
ADD CONSTRAINT [FK_DetalleViviendaEstadoCalidad]
    FOREIGN KEY ([EstadoCalidadEstadoId])
    REFERENCES [dbo].[EstadoCalidad]
        ([EstadoId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DetalleViviendaEstadoCalidad'
CREATE INDEX [IX_FK_DetalleViviendaEstadoCalidad]
ON [dbo].[DetalleInmueble_DetalleVivienda]
    ([EstadoCalidadEstadoId]);
GO

-- Creating foreign key on [EdificioId] in table 'Edificio'
ALTER TABLE [dbo].[Edificio]
ADD CONSTRAINT [FK_InmuebleEdificio]
    FOREIGN KEY ([EdificioId])
    REFERENCES [dbo].[Inmueble]
        ([PropertyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [MunicipioMunicipioId] in table 'Inmueble'
ALTER TABLE [dbo].[Inmueble]
ADD CONSTRAINT [FK_InmuebleMunicipio]
    FOREIGN KEY ([MunicipioMunicipioId])
    REFERENCES [dbo].[Municipio]
        ([MunicipioId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InmuebleMunicipio'
CREATE INDEX [IX_FK_InmuebleMunicipio]
ON [dbo].[Inmueble]
    ([MunicipioMunicipioId]);
GO

-- Creating foreign key on [InmueblePropertyId] in table 'Tasacion'
ALTER TABLE [dbo].[Tasacion]
ADD CONSTRAINT [FK_InmuebleTasacion]
    FOREIGN KEY ([InmueblePropertyId])
    REFERENCES [dbo].[Inmueble]
        ([PropertyId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InmuebleTasacion'
CREATE INDEX [IX_FK_InmuebleTasacion]
ON [dbo].[Tasacion]
    ([InmueblePropertyId]);
GO

-- Creating foreign key on [TasacionTasacionId] in table 'Inmueble'
ALTER TABLE [dbo].[Inmueble]
ADD CONSTRAINT [FK_TasacionBasaEnInmueble]
    FOREIGN KEY ([TasacionTasacionId])
    REFERENCES [dbo].[Tasacion]
        ([TasacionId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TasacionBasaEnInmueble'
CREATE INDEX [IX_FK_TasacionBasaEnInmueble]
ON [dbo].[Inmueble]
    ([TasacionTasacionId]);
GO

-- Creating foreign key on [ProvinciaProvinciaId] in table 'Municipio'
ALTER TABLE [dbo].[Municipio]
ADD CONSTRAINT [FK_ProvinciaMunicipio]
    FOREIGN KEY ([ProvinciaProvinciaId])
    REFERENCES [dbo].[Provincia]
        ([ProvinciaId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProvinciaMunicipio'
CREATE INDEX [IX_FK_ProvinciaMunicipio]
ON [dbo].[Municipio]
    ([ProvinciaProvinciaId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------